import sqlite3

class DbManager(object):

    def __init__(self, *args):
        self.db = sqlite3.connect(*args)
        self.cursor = self.db.cursor()

    def __enter__(self):
        return self.cursor

    def __exit__(self, types, value, traceback):
        self.db.commit()
        return False

    def __del__(self):
        self.db.commit()
        self.db.close()

    def switchDb(self, *args):
        self.db.close()

        self.db = sqlite3.connect(*args)
        self.cursor = self.db.cursor()

    def createTable(self, tableString):
        self.cursor.execute(tableString)
        self.db.commit()

    def commitAndClose(self):
        self.db.commit()
        self.db.close()

class SQLiteDb(DbManager):

    douyinUsers = """
    CREATE TABLE IF NOT EXISTS douyinUsers(
        id INTEGER PRIMARY KEY autoincrement,
        userId TEXT,
        userSecId TEXT UNIQUE,
        userName TEXT,
        shopSales TEXT,
        shopUrl TEXT,
        lives TEXT,
        fans TEXT,
        tm TEXT
    )"""

    def __init__(self, database='', *args):
        super().__init__(database, *args)
        self.initDb()

    def initDb(self):
        self.createTable(self.douyinUsers)

    def addUsers(self, userId, userSecId, userName, shopSales, shopUrl, lives, fans, tm):
        try:
            insertData = self.cursor.execute("""INSERT OR IGNORE INTO douyinUsers 
                (userId, userSecId, userName, shopSales, shopUrl, lives, fans, tm) VALUES 
                ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}')
                """.format(userId, userSecId, userName, shopSales, shopUrl, lives, fans, tm))
        except:
            updateData = self.cursor.execute("""UPDATE douyinUsers 
                SET shopSales = '{0}', lives = '{1}', fans = '{2}' 
                WHERE userSecId = '{3}'
                """.format(shopSales, lives, fans, userSecId))

        self.db.commit()

    def delUsers(self, uid):
        self.cursor.execute("""DELETE FROM douyinUsers WHERE id={}""".format(uid))
        self.db.commit()

    def fetchUsers(self, limit = 50, page = 1):
        self.cursor.execute("""SELECT * FROM douyinUsers 
            ORDER BY id DESC LIMIT {0} OFFSET {1}""".format(limit, (page - 1)*limit))
        return self.cursor.fetchall()
